package com.github.wxhnyfy.service.impl;

import com.github.wxhnyfy.ExcelData;
import com.github.wxhnyfy.NoHeaderSheetData;
import com.github.wxhnyfy.SheetData;
import com.github.wxhnyfy.service.ExcelToolService;
import org.apache.commons.lang3.StringUtils;
import org.dhatim.fastexcel.Color;
import org.dhatim.fastexcel.Workbook;
import org.dhatim.fastexcel.Worksheet;
import org.dhatim.fastexcel.reader.ReadableWorkbook;
import org.dhatim.fastexcel.reader.Row;
import org.dhatim.fastexcel.reader.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Stream;

/**
 * dhatim-fast-excel-tool
 *
 * @author chenwc
 */
public class FastExcelTool implements ExcelToolService {

    private static final Logger logger = LoggerFactory.getLogger(FastExcelTool.class);

    /**
     * 数据转换
     *
     * @param rows        读取到的行数据
     * @param headerList  读取到的表头
     * @param startRow    开始读取的行号（从0开始）
     * @param endRow      结束读取的行号（从0开始）
     * @param startColumn 开始读取的列数（从0开始）
     * @param endColumn   结束读取的总列数（从0开始）
     * @return 转换后的数据 List<Map<String, String>>
     */
    private static List<Map<String, String>> getDataMapList(List<Row> rows, List<String> headerList, int startRow, int endRow, int startColumn, int endColumn) {
        List<Map<String, String>> dataMapList = new ArrayList<>();
        for (int i = startRow; i < endRow; i++) {
            Row row = rows.get(i);
            Map<String, String> rowMap = new HashMap<>();
            for (int j = startColumn; j < endColumn; j++) {
                if (j > row.getCellCount()) {
                    rowMap.put(headerList.get(j), "");
                } else {
                    rowMap.put(headerList.get(j), row.getCellText(j));
                }
            }
            dataMapList.add(rowMap);
        }
        return dataMapList;
    }

    /**
     * 给工作表写入数据
     *
     * @param ws          Worksheet
     * @param header      表头
     * @param dataMapList 行数据
     */
    private static void setWorksheetData(Worksheet ws, List<String> header, List<Map<String, String>> dataMapList) {
        logger.info(">>>>>写入表头....");
        // 第一行为表头
        for (int j = 0; j < header.size(); j++) {
            ws.value(0, j, header.get(j));
        }
        logger.info(">>>>>写入表头完成....");
        logger.info(">>>>>写入数据行....");
        //第二行开始为数据
        for (int i = 0; i < dataMapList.size(); i++) {
            for (int j = 0; j < header.size(); j++) {
                ws.value(i + 1, j, dataMapList.get(i).get(header.get(j)));
            }
        }
        logger.info(">>>>>写入数据行完成....");
    }

    /**
     * 读取Excel文件第 1 个Sheet
     *
     * @param excelFile Excel文件
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile) {
        return read(excelFile, 0);
    }

    /**
     * 读取整个Excel文件
     *
     * @param excelFile Excel文件
     * @return Excel数据
     */
    @Override
    public ExcelData readExcelFile(File excelFile) {
        ExcelData excelData = new ExcelData();
        excelData.setFileName(excelFile.getName());
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Stream<Sheet> sheets = wb.getSheets();
            AtomicInteger cnt = new AtomicInteger();
            Map<Integer, String> sheetNameMap = new HashMap<>();
            List<SheetData> sheetDataList = new ArrayList<>();
            sheets.forEach(sheet -> {
                int sheetIndex = cnt.getAndIncrement();
                sheetNameMap.put(sheetIndex, sheet.getName());
                SheetData sheetData = new SheetData();
                sheetData.setSheetName(sheet.getName());
                List<Row> rows;
                try {
                    rows = sheet.read();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
                Row header = rows.get(0);
                List<String> headerList = new ArrayList<>();
                for (int i = 0; i < header.getCellCount(); i++) {
                    headerList.add(header.getCellText(i));
                }
                // 去掉表头
                rows.remove(header);
                sheetData.setHeader(headerList);
                List<Map<String, String>> dataMapList = getDataMapList(rows, headerList, 0, rows.size(), 0, headerList.size());
                sheetData.setDataMapList(dataMapList);
                sheetDataList.add(sheetData);
            });
            excelData.setSheetSize(cnt.get());
            excelData.setSheetNameMap(sheetNameMap);
            excelData.setSheetDataList(sheetDataList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return excelData;
    }

    /**
     * 指定Sheet名称读取Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetName Sheet名称
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, String sheetName) {
        SheetData sheetData = new SheetData();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Optional<Sheet> sheetOptional = wb.findSheet(sheetName);
            if (!sheetOptional.isPresent()) {
                throw new RuntimeException("读取Sheet数据失败！");
            }
            Sheet sheet = sheetOptional.get();
            sheetData.setSheetName(sheet.getName());
            List<Row> rows = sheet.read();
            Row header = rows.get(0);
            List<String> headerList = new ArrayList<>();
            for (int i = 0; i < header.getCellCount(); i++) {
                headerList.add(header.getCellText(i));
            }
            // 去掉表头
            rows.remove(header);
            sheetData.setHeader(headerList);
            List<Map<String, String>> dataMapList = getDataMapList(rows, headerList, 0, rows.size(), 0, headerList.size());
            sheetData.setDataMapList(dataMapList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return sheetData;
    }

    /**
     * 读取Excel文件第 sheetIndex 个Sheet
     *
     * @param excelFile  Excel文件
     * @param sheetIndex Sheet下标
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex) {
        SheetData sheetData = new SheetData();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Optional<Sheet> sheetOptional = wb.getSheet(sheetIndex);
            if (!sheetOptional.isPresent()) {
                throw new RuntimeException("读取Sheet数据失败！");
            }
            Sheet sheet = sheetOptional.get();
            sheetData.setSheetName(sheet.getName());
            List<Row> rows = sheet.read();
            Row header = rows.get(0);
            List<String> headerList = new ArrayList<>();
            for (int i = 0; i < header.getCellCount(); i++) {
                headerList.add(header.getCellText(i));
            }
            // 去掉表头
            rows.remove(header);
            sheetData.setHeader(headerList);
            List<Map<String, String>> dataMapList = getDataMapList(rows, headerList, 0, rows.size(), 0, headerList.size());
            sheetData.setDataMapList(dataMapList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return sheetData;
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public SheetData readByNoHeaderSheet(File excelFile, int sheetIndex) {
        SheetData sheetData = new SheetData();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Optional<Sheet> sheetOptional = wb.getSheet(sheetIndex);
            if (!sheetOptional.isPresent()) {
                throw new RuntimeException("读取Sheet数据失败！");
            }
            Sheet sheet = sheetOptional.get();
            sheetData.setSheetName(sheet.getName());
            List<Row> rows = sheet.read();
            Row header = rows.get(0);

            List<String> headerList = new ArrayList<>();
            for (int i = 0; i < header.getCellCount(); i++) {
                headerList.add(String.valueOf(i));
            }
            sheetData.setHeader(headerList);

            List<Map<String, String>> dataMapList = new ArrayList<>();
            for (Row row : rows) {
                Map<String, String> rowMap = new HashMap<>();
                for (int j = 0; j < headerList.size(); j++) {
                    rowMap.put(headerList.get(j), row.getCellText(j));
                }
                dataMapList.add(rowMap);
            }
            sheetData.setDataMapList(dataMapList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return sheetData;
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public NoHeaderSheetData readByNoHeaderSheetData(File excelFile, int sheetIndex) {
        NoHeaderSheetData sheetData = new NoHeaderSheetData();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Optional<Sheet> sheetOptional = wb.getSheet(sheetIndex);
            if (!sheetOptional.isPresent()) {
                throw new RuntimeException("读取Sheet数据失败！");
            }
            Sheet sheet = sheetOptional.get();
            sheetData.setSheetName(sheet.getName());
            List<Row> rows = sheet.read();
            List<List<String>> dataList = new ArrayList<>();
            for (Row row : rows) {
                List<String> rowList = new ArrayList<>();
                for (int j = 0; j < row.getCellCount(); j++) {
                    rowList.add(row.getCellText(j));
                }
                dataList.add(rowList);
            }
            sheetData.setDataList(dataList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return sheetData;
    }

    /**
     * 自定义读取excel文件的sheet
     *
     * @param excelFile   excel文件
     * @param sheetIndex  要读取的sheet页签下标（从0开始）
     * @param startRow    开始读取的行号（从0开始）
     * @param endRow      结束读取的行号（从0开始）
     * @param startColumn 开始读取的列数（从0开始）
     * @param endColumn   结束读取的总列数（从0开始）
     * @return excel文件数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex, int startRow, int endRow, int startColumn, int endColumn) {
        SheetData sheetData = new SheetData();
        try (InputStream is = Files.newInputStream(excelFile.toPath()); ReadableWorkbook wb = new ReadableWorkbook(is)) {
            Optional<Sheet> sheetOptional = wb.getSheet(sheetIndex);
            if (!sheetOptional.isPresent()) {
                throw new RuntimeException("读取Sheet数据失败！");
            }
            Sheet sheet = sheetOptional.get();
            sheetData.setSheetName(sheet.getName());
            List<Row> rows = sheet.read();
            if (endRow >= rows.size()) {
                endRow = rows.size() - 1;
            }
            List<String> headerList = new ArrayList<>();
            for (int i = startColumn; i <= endColumn; i++) {
                headerList.add(String.valueOf(i));
            }
            sheetData.setHeader(headerList);

            List<Map<String, String>> dataMapList = new ArrayList<>();
            for (int i = startRow; i < endRow; i++) {
                Row row = rows.get(i);
                Map<String, String> rowMap = new HashMap<>();
                for (int j = 0, k = startColumn; j < headerList.size(); j++, k++) {
                    rowMap.put(headerList.get(j), row.getCellText(k));
                }
                dataMapList.add(rowMap);
            }
            sheetData.setDataMapList(dataMapList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
        return sheetData;
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void write(File excelFile, SheetData sheetData) {
        try (OutputStream os = Files.newOutputStream(excelFile.toPath()); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
            logger.info(">>>>>写入Excel文件：{}", excelFile.getAbsolutePath());
            // 设置全局默认字体
            wb.setGlobalDefaultFont("宋体", 12);
            logger.info(">>>>>设置全局默认字体为宋体，字号12");
            if (StringUtils.isEmpty(sheetData.getSheetName())) {
                sheetData.setSheetName("Sheet1");
            }
            Worksheet ws = wb.newWorksheet(sheetData.getSheetName());
            List<String> header = sheetData.getHeader();
            List<Map<String, String>> dataMapList = sheetData.getDataMapList();
            // 冻结第1行
            ws.freezePane(0, 1);
            logger.info(">>>>>冻结表头....");
            // 命名单元格区域（单元格区域的名称只能包含字母、数字和下划线）
            ws.range(0, 0, 0, header.size() - 1).setName("header");
            // 在单元格区域设置样式
            ws.range(0, 0, 0, header.size() - 1).style().horizontalAlignment("center").italic().set();
            // 设置字体颜色和加粗显示
            ws.range(0, 0, 0, header.size() - 1).style().bold().fontColor(Color.RED).set();
            logger.info(">>>>>设置表头样式....");
            //开启筛选
            ws.setAutoFilter(0, 0, header.size() - 1);
            logger.info(">>>>>开启筛选....");
            setWorksheetData(ws, header, dataMapList);
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void writeNoHeaderSheet(File excelFile, NoHeaderSheetData sheetData) {
        try (OutputStream os = Files.newOutputStream(excelFile.toPath()); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
            logger.info(">>>>>写入Excel文件：{}", excelFile.getAbsolutePath());
            // 设置全局默认字体
            wb.setGlobalDefaultFont("宋体", 12);
            logger.info(">>>>>设置全局默认字体为宋体，字号12");
            if (StringUtils.isEmpty(sheetData.getSheetName())) {
                sheetData.setSheetName("Sheet1");
            }
            Worksheet ws = wb.newWorksheet(sheetData.getSheetName());

            List<List<String>> dataList = sheetData.getDataList();
            logger.info(">>>>>写入数据行....");
            //第二行开始为数据
            for (int i = 0; i < dataList.size(); i++) {
                for (int j = 0; j < dataList.get(i).size(); j++) {
                    ws.value(i, j, dataList.get(i).get(j));
                }
            }
            logger.info(">>>>>写入数据行完成....");
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
    }

    /**
     * 同一个Excel文件写入多个Sheet
     * 每个工作表由不同的线程生成
     *
     * @param excelFile     Excel文件
     * @param sheetDataList Sheet数据
     */
    @Override
    public void writeMultipleSheet(File excelFile, List<SheetData> sheetDataList) {
        try (OutputStream os = Files.newOutputStream(excelFile.toPath()); Workbook wb = new Workbook(os, "MyApplication", "1.0")) {
            logger.info(">>>>>写入Excel文件：{}", excelFile.getAbsolutePath());
            // 设置全局默认字体
            wb.setGlobalDefaultFont("宋体", 12);
            logger.info(">>>>>设置全局默认字体为宋体，字号12");
            List<CompletableFuture<Void>> futureList = new ArrayList<>(sheetDataList.size());
            int sheetIndex = 1;
            for (SheetData sheetData : sheetDataList) {
                if (StringUtils.isEmpty(sheetData.getSheetName())) {
                    sheetData.setSheetName("Sheet" + sheetIndex);
                }
                sheetIndex++;
                Worksheet ws = wb.newWorksheet(sheetData.getSheetName());
                CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> {
                    List<String> header = sheetData.getHeader();
                    List<Map<String, String>> dataMapList = sheetData.getDataMapList();
                    // 冻结第1行
                    ws.freezePane(0, 1);
                    logger.info(">>>>>冻结表头....");
                    // 命名单元格区域（单元格区域的名称只能包含字母、数字和下划线）
                    ws.range(0, 0, 0, header.size() - 1).setName("header");
                    // 在单元格区域设置样式
                    ws.range(0, 0, 0, header.size() - 1).style().horizontalAlignment("center").italic().set();
                    // 设置字体颜色和加粗显示
                    ws.range(0, 0, 0, header.size() - 1).style().bold().fontColor(Color.RED).set();
                    logger.info(">>>>>设置表头样式....");
                    //开启筛选
                    ws.setAutoFilter(0, 0, header.size() - 1);
                    logger.info(">>>>>开启筛选....");
                    setWorksheetData(ws, header, dataMapList);
                });
                futureList.add(cf);
            }
            // 等待所有线程完成
            CompletableFuture.allOf(futureList.toArray(new CompletableFuture[0])).get();
        } catch (Exception e) {
            logger.error("Error: ", e);
        }
    }
}
